IF exists(SELECT * FROM dbo.sysobjects WHERE name='afwezigenTotalen' AND xtype='V') 
BEGIN
	DROP VIEW afwezigenTotalen
END
GO

CREATE view afwezigenTotalen
as
select l.naamcode,l.achternaam + ' ' + l.voornaam as volleNaam,

sum(case when reden = 'Z' and dagdeel = 'G' then 2
		when reden = 'Z' and dagdeel = 'V' then 1
		when reden = 'Z' and dagdeel = 'N' then 1
		else 0 end) as aantalZ,	
sum(case when reden = 'B' and dagdeel = 'G' then 2
		when reden = 'B' and dagdeel = 'V' then 1
		when reden = 'B' and dagdeel = 'N' then 1
		else 0 end) as aantalB,
sum(case when reden = 'O' and dagdeel = 'G' then 2
		when reden = 'O' and dagdeel = 'V' then 1
		when reden = 'O' and dagdeel = 'N' then 1
		else 0 end) as aantalO,
sum(case when reden = 'S' and dagdeel = 'G' then 2
		when reden = 'S' and dagdeel = 'V' then 1
		when reden = 'S' and dagdeel = 'N' then 1
		else 0 end) as aantalS,
sum(case when reden = 'E' and dagdeel = 'G' then 2
		when reden = 'E' and dagdeel = 'V' then 1
		when reden = 'E' and dagdeel = 'N' then 1
		else 0 end) as aantalE,	
sum(case when reden = 'N' and dagdeel = 'G' then 2
		when reden = 'N' and dagdeel = 'V' then 1
		when reden = 'N' and dagdeel = 'N' then 1
		else 0 end) as aantalN,

sum(case when (reden is null or (not(reden = '|') and not(reden = 'X')))
			and dagdeel = 'G' then 2
	when (reden is null or (not(reden = '|') and not(reden = 'X')))
			and dagdeel = 'V' then 1
	when (reden is null or (not(reden = '|') and not(reden = 'X')))
			and dagdeel = 'N' then 1
		else 0 end) as aantal
from afwezigheden as a left join leraars as l
on l.naamcode = a.naamcode
group by l.naamcode,l.achternaam,l.voornaam